package com.bearcats.product.action;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

import org.apache.commons.lang.StringUtils;

import com.bearcats.product.model.Product;
import com.bearcats.product.model.ProductDetail;
import com.sendi.common.action.BaseAction;
import com.sendi.common.util.JExcelUtils;
import com.sendi.common.util.Page;
import com.sendi.system.util.common.DateUtil;

public class ProductAction extends BaseAction{
	private Long productId;
	private Product product;
	private String startTime;
	private String endTime;
	private String order;
	private List<ProductDetail> details;
	
	public Product getProduct() {
		return product;
	}
	public void setProduct(Product product) {
		this.product = product;
	}
	public List<ProductDetail> getDetails() {
		return details;
	}
	public void setDetails(List<ProductDetail> details) {
		this.details = details;
	}
	public Long getProductId() {
		return productId;
	}
	public void setProductId(Long productId) {
		this.productId = productId;
	}
	public String getStartTime() {
		return startTime;
	}
	public void setStartTime(String startTime) {
		this.startTime = startTime;
	}
	public String getEndTime() {
		return endTime;
	}
	public void setEndTime(String endTime) {
		this.endTime = endTime;
	}
	public String getOrder() {
		return order;
	}
	public void setOrder(String order) {
		this.order = order;
	}
	public String save()throws Exception{
		if(product != null 
				&& details != null
				&& details.size() > 0){
			if(StringUtils.isEmpty(product.getOutTime())){
				product.setOutTime(DateUtil.getCurDateStr("yyyy-MM-dd HH:mm:ss"));
			}
			if(StringUtils.isEmpty(product.getProductCode())){
				product.setProductCode(getCode());
			}
			product.setOprTime(DateUtil.getCurDateStr("yyyyMMddHHmmss"));
			product.setDetails(details);
			for(ProductDetail detail : details){
				detail.setProduct(product);
			}
			serviceSupportImp.save(product);
			System.out.println("id:"+product.getId().toString());
			writeResponseText(product.getId().toString());
		}
		return null;
	}
	
	public String query()throws Exception{
		if(product != null){
			StringBuffer hql = new StringBuffer("from ProductJ");
			StringBuffer condition = new StringBuffer();
			if(StringUtils.isNotEmpty(product.getProductCode())){
				condition.append(" productCode like '%").append(product.getProductCode()).append("%' and");
			}
			if(StringUtils.isNotEmpty(product.getCusName())){
				condition.append(" cusName like '%").append(product.getCusName()).append("%' and");
			}
			if(StringUtils.isNotEmpty(product.getLinker())){
				condition.append(" linker like '%").append(product.getLinker()).append("%' and");
			}
			if(StringUtils.isNotEmpty(product.getSaleType())){
				condition.append(" saleType like '%").append(product.getSaleType()).append("%' and");
			}
			if(StringUtils.isNotEmpty(product.getPhoneNo())){
				condition.append(" phoneNo like '%").append(product.getPhoneNo()).append("%' and");
			}
			if(StringUtils.isNotEmpty(startTime)){
				condition.append(" outTime >= '").append(startTime).append("' and");
			}
			if(StringUtils.isNotEmpty(endTime)){
				condition.append(" outTime < '").append(endTime).append("' and");
			}
			if(condition.length() > 3 && condition.toString().endsWith("and")){
				hql.append(" where ").append(condition.substring(0, condition.length() -3));
			}
			System.out.println(hql.toString());
			Page page = serviceSupport.getPageByHql(hql.toString(), start, limit, null);
			writePage(page);
		}
		return null;
	}
	
	public void export()throws Exception{
		if(productId != null && productId !=0){
			Object obj = serviceSupportImp.get(Product.class,productId);
			if(obj != null){
				product = (Product)obj;
				details = product.getDetails();
				int size = 0;
				if(details != null)
					size = details.size();
				//details = serviceSupportImp.findByHQL("from ProductDetail where project.id = "+product.getId());
				DecimalFormat df = new DecimalFormat("#.#");
				String sheetName="强力缆绳有限公司出货单";
			 	JExcelUtils jExcelUtils = new JExcelUtils();
			 	jExcelUtils.createExcel(getResponse(),sheetName);
			 	jExcelUtils.getSheet().getSettings().setDefaultColumnWidth(7);
			 	jExcelUtils.setTitleFormat();
			 	jExcelUtils.mergeCellsAndInsertData(0,0,10,0,sheetName); //合并单元表格并插入标题
			 	jExcelUtils.setRow(1);
			 	jExcelUtils.setDataFormat();
			 	jExcelUtils.mergeCellsAndInsertData(0,1,3,1,"客户名称："+product.getCusName());
//			 	jExcelUtils.mergeCellsAndInsertData(4,1,5,1,"取货人："+product.getLinker());
			 	jExcelUtils.mergeCellsAndInsertData(4,1,6,1,"电话："+product.getPhoneNo());
			 	jExcelUtils.mergeCellsAndInsertData(7,1,10,1,"出货日期："+product.getOutTime());
			 	jExcelUtils.setRow(2);
			 	jExcelUtils.setDataFormat();
			 	// export details
			 	String productName = "";
			 	List<ProductDetail> printList = new ArrayList<ProductDetail>();
			 	for(ProductDetail detail : details){
			 		if(!productName.equals(detail.getProductName())){
			 			printDetails(printList,jExcelUtils);
			 			productName = detail.getProductName();
			 			printList.clear();
			 		}
			 		printList.add(detail);
			 	}
			 	if(printList.size() > 0){
			 		printDetails(printList,jExcelUtils);
			 	}
			 	int row = jExcelUtils.getRow();
			 	jExcelUtils.mergeCellsAndInsertData(0,row,2,row,"总重量(KG)："+df.format(product.getTotalWeight()));
			 	jExcelUtils.mergeCellsAndInsertData(3,row,5,row,"总金额(元)："+df.format(product.getTotalPrice()));
			 	jExcelUtils.mergeCellsAndInsertData(6,row,7,row,"件数："+size);
			 	jExcelUtils.mergeCellsAndInsertData(8,row,10,row,"其他：");
			 	row++;
			 	jExcelUtils.mergeCellsAndInsertData(0,row,10,row+1,"取货人:        ");
			 	row = row+2;
			 	jExcelUtils.mergeCellsAndInsertData(0,row,2,row,"电话：2288727");
			 	jExcelUtils.mergeCellsAndInsertData(3,row,5,row,"传真：3996933");
			 	jExcelUtils.mergeCellsAndInsertData(6,row,10,row,"手机：13702885623");
			 	jExcelUtils.closeExcelFile();
			}
		}else{
			this.writeResponseText("没有数据或计算出错！");
		}
	}
	
	public void printDetails(List<ProductDetail> details,JExcelUtils jExcelUtils){
		int startRow = jExcelUtils.getRow();
		String[][] detailContent = getPriceMatrix(details);
		if(detailContent != null){
			int length = detailContent.length;
			jExcelUtils.mergeCellsAndInsertData(0,startRow,0,startRow+length-1,details.get(0).getProductName()); //合并单元表格并插入标题
//			jExcelUtils.setRow(++startRow);
//			jExcelUtils.insertData(detailContent);
			for(String[] row:detailContent){
				jExcelUtils.insertRowData(1,row);
			}
			float price = details.get(0).getPrice(),tWeight = 0.0f,tPrice = 0.0f;
			for(ProductDetail detail : details){
				tWeight += detail.getWeight();
			}
			tPrice = tWeight * price;
			DecimalFormat df = new DecimalFormat("#.#");
			int row = jExcelUtils.getRow();
			jExcelUtils.mergeCellsAndInsertData(0,row,4,row," ");
			jExcelUtils.mergeCellsAndInsertData(5,row,6,row,"单价(元)："+ df.format(price));
			jExcelUtils.mergeCellsAndInsertData(7,row,8,row,"重量(KG)："+ df.format(tWeight));
			jExcelUtils.mergeCellsAndInsertData(9,row,10,row,"金额(元)："+ df.format(tPrice));
			jExcelUtils.setRow(++row);
		}
	}
	
	public String[][] getPriceMatrix(List<ProductDetail> details){
		String[][] strings = null;
		if(details != null && details.size() > 0){
			int size = details.size();
			int row = size/10;
			if(size % 10 != 0){
				row++;
			}
			strings = new String[row][10];
			int index = 0;
			for(int i=0;i<strings.length;i++){
				for(int j=0;j<10;j++){
					if(index < details.size()){
						ProductDetail detail = details.get(index);
						strings[i][j] = ""+detail.getWeight();
					}else{
						strings[i][j] = "";
					}
					index++;
				}
			}
		}
		return strings;
	}
	public String delete()throws Exception{
		Object obj = serviceSupportImp.get(Product.class, productId);
		if(obj != null){
			serviceSupportImp.delete(obj);
			writeResponseText("success");
			return null;
		}
		writeResponseText("fail");
		return null;
	}
	
	public String productStatisByCus()throws Exception{
		String sql = "select cus_name,sum(total_weight) total_weight,sum(total_price) total_price from product where 1 = 1 "; 
		if(product  != null && StringUtils.isNotBlank(product.getCusName()))
			sql = sql + " and cus_name like '%"+product.getCusName()+"%' ";
		if(StringUtils.isNotEmpty(startTime)){
			sql = sql + " and out_time >= '"+startTime+"' ";
		}
		if(StringUtils.isNotEmpty(endTime)){
			sql = sql + " and out_time <= '"+endTime+"' ";
		}
		sql = sql + " group by cus_name order by total_price desc limit 20";
		Connection conn = dataSource.getConnection();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		StringBuilder cusNames = new StringBuilder();
		StringBuilder prices = new StringBuilder();
		StringBuilder weights = new StringBuilder();
		while(rs.next()){
			cusNames.append("<category label=\"").append(rs.getString(1)).append("\"/>");
			prices.append("<set value=\"").append(rs.getString(3)).append("\"/>");
			weights.append("<set value=\"").append(rs.getString(2)).append("\"/>");
		}
		rs.close();
		stmt.close();
		conn.close();
		StringBuilder result = new StringBuilder();
		result.append("<categories>").append(cusNames).append("</categories>");
		result.append("<dataset seriesname=\"总价\">").append(prices).append("</dataset>");
		result.append("<dataset seriesname=\"重量\">").append(weights).append("</dataset>");
		writeResponseText(result.toString());
		return null;
	}
	
	public String productStatisByType()throws Exception{
		// select masterId,product_name,sum(weight) weight,sum(weight*price) total from product_detail group by masterId,product_name;
		// select id, substring(out_time,3,5) from product where 1=1
		// select product_name,sum(weight) weight,sum(total)  total from 
		StringBuilder sql = new StringBuilder("select out_time,sum(total)  total,sum(weight) weight from ");
		sql.append("(select id, substring(out_time,3,5) out_time from product where 1=1");
		if(StringUtils.isNotEmpty(startTime)){
			sql.append(" and out_time >= '"+startTime+"' ");
		}
		if(StringUtils.isNotEmpty(endTime)){
			sql.append(" and out_time <= '"+endTime+"' ");
		}
		sql.append(") a,") ;
		sql.append("(select masterId,sum(weight) weight,sum(weight*price) total from product_detail where product_name='")
				.append(product.getProductCode()).append("' group by masterId) b ");
		sql.append(" where a.id = b.masterId group by out_time order by out_time");
		System.out.println(sql);
		Connection conn = dataSource.getConnection();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql.toString());
		StringBuilder cusNames = new StringBuilder();
		StringBuilder prices = new StringBuilder();
		StringBuilder weights = new StringBuilder();
		while(rs.next()){
			cusNames.append("<category label=\"").append(rs.getString(1)).append("\"/>");
			prices.append("<set value=\"").append(rs.getString(3)).append("\"/>");
			weights.append("<set value=\"").append(rs.getString(2)).append("\"/>");
		}
		rs.close();
		stmt.close();
		conn.close();
		StringBuilder result = new StringBuilder();
		result.append("<categories>").append(cusNames).append("</categories>");
		result.append("<dataset seriesname=\"总价\">").append(prices).append("</dataset>");
		result.append("<dataset seriesname=\"重量\">").append(weights).append("</dataset>");
		writeResponseText(result.toString());
		return null;
	}
	
	private String getCode(){
		SimpleDateFormat dateformat1=new SimpleDateFormat("yyyyMMddHHmmss");
		String code = "QL"+dateformat1.format(new Date());
		Random ran = new Random();
		for(int i=0;i<4;i++){
			code = code + ran.nextInt(10);
		}
		return code;
	}
}
